IF exists(SELECT * FROM dbo.sysobjects WHERE name='WEB_vakrapportKlasOverzicht' AND xtype='P') 
BEGIN
	DROP PROCEDURE WEB_vakrapportKlasOverzicht
END
GO

CREATE   procedure WEB_vakrapportKlasOverzicht
	(@klas varchar(5),
	 @periode varchar(2))
as
set nocount on

select vib.code,vib.vak into #overschot from vakrapportIndividueleRekenwijze as vib
  left join vakrapportOpdrachtCodes as vc
    on vc.code = vib.code 
		and vc.klas=vib.klas 
		and vc.vak = vib.vak 
		and vc.periode = vib.periode
where vib.klas = @klas
	  and vib.periode=@periode
	    and vc.code is null

select 1 as volgNr,'a' as vak,'c' as code,
	'<?xml version="1.0"  encoding="ISO-8859-1" ?>' +
	'<vakrapport iNr="1234567" periode="m4">' +
	'<leerling>' +
	'<vnaam>Bart</vnaam>' +
	'<anaam>Onbekend</anaam>' +
	'<klas>' + @klas + '</klas>' +
	'</leerling>' +
	'<vakken>' as tekst
union
  select distinct 2 as volgNr,vak as vak,'aaaaa' as code,
	'<' + lower(vak) + ' leraar="doe' + upper(vak) + '">'
	from vakrapportIndividueleRekenwijze 
  where klas = @klas
	  and periode=@periode
union
  select distinct 2,vak,'zzzzz','</' + lower(vak) + '>'
	from vakrapportIndividueleRekenwijze 
  where klas = @klas
	  and periode=@periode
union
  select 2,vak,code, '<' + lower(code) + '>' + code + '*</' + lower(code) + '>' 
	from vakrapportopdrachtcodes
  where klas=@klas 
	  and periode=@periode
union 
  select distinct 2,vak,code, '<' + lower(code) + 'com>' + code + ' - detail </' + lower(code) + 'com>' 
	from vakrapportopdrachtcodes
  where klas=@klas
	  and periode=@periode
union 
  select distinct 2,vak,code,'<' + lower(code) + '>' + code + '</' + lower(code) + '>' 
	from #overschot
union
  select distinct 2,vak,code,'<' + lower(code) + 'gem>' + code + '</' + lower(code) + 'gem>' 
  	from vakrapportCollectieveRekenwijze
  where klas = @klas  
	  and periode=@periode
union
  select 3,'z','z','</vakken></vakrapport>'
order by volgNr,vak,code